package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import model.Alocacao;
import model.Computador;
import model.Usuario;

public class AlocacaoDAO extends PersistenciaDAO {

	public Alocacao buscarAlocacaoPorIdComputador(int id) throws SQLException {
		Alocacao result = null;
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM alocacao WHERE cd_computador = "
						+ id + " AND dt_saida is null");

		while (elements.next()) {
			result = montarAlocacao(elements);
		}
		return result;
	}

	public Collection<Alocacao> buscarAlocacoes() throws SQLException {

		Collection<Alocacao> result = new ArrayList<Alocacao>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM quiosque.alocacao WHERE dt_saida is null");

		while (elements.next()) {
			Alocacao alocacao = montarAlocacao(elements);

			result.add(alocacao);
		}
		return result;
	}

	public Alocacao montarAlocacao(ResultSet elements) throws SQLException {
		Alocacao alocacao = new Alocacao();

		Computador computador = new ComputadorDAO().buscarComputadorPorId(elements.getInt("cd_computador"));
		alocacao.setComputador(computador);
		Usuario usuario = new UsuarioDAO().buscarUsuarioPorId(elements.getInt("cd_usuario"));
		alocacao.setUsuario(usuario);
		alocacao.setDataEntrada(new Date(elements.getTimestamp("dt_entrada").getTime()));
		if (elements.getDate("dt_saida") != null){
			alocacao.setDataSaida(new Date(elements.getTimestamp("dt_saida").getTime()));	
		} else {
			alocacao.setDataSaida(null);
		}
		
		alocacao.setId(elements.getInt("cd_alocacao"));

		return alocacao;
	}

	public Collection<Integer> buscarMesesDeAlocacao() throws SQLException {
		Collection<Integer> meses = new ArrayList<Integer>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("	SELECT distinct(month(dt_entrada)) as mes FROM quiosque.alocacao ");

		while (elements.next()) {
			Integer mes = elements.getInt("mes");
			meses.add(mes);
		}
		return meses;
	}

	public Collection<Alocacao> buscarAlocacoesPorMes(int mes) throws SQLException {
		Collection<Alocacao> result = new ArrayList<Alocacao>();
		Statement sta = connection.createStatement();
		ResultSet elements = sta
				.executeQuery("SELECT * FROM quiosque.alocacao where MONTH(dt_entrada) = " + mes);

		while (elements.next()) {
			Alocacao alocacao = montarAlocacao(elements);
			result.add(alocacao);
		}
		return result;
	}
}
